import xlrd
import pymysql
import os
#打开数据所在的工作簿，以及选择存有数据的工作表
abspath = os.path.abspath(__file__)
file_path = os.path.join(os.path.split(abspath)[0], "family.xls")
book = xlrd.open_workbook(file_path)
sheet = book.sheet_by_name("Sheet1")
#建立一个MySQL连接
conn = pymysql.connect(
        host='localhost',
        user='root',
        passwd='jipengtech',
        db = 'db_library',
        port=3306,
        charset='utf8'
        )
# 获得游标
cur = conn.cursor()

# SQL 插入语句
sql = "CREATE TABLE IF NOT EXISTS test(" \
      "id INT," \
      "name VARCHAR(10)," \
      "age INT," \
      "primary key(id))"
cur.execute(sql)



w = "INSERT INTO test(id,name,age)VALUES (%s,%s,%s)"
for i in range(1,sheet.nrows):
    id = sheet.cell(i,0).value
    name = sheet.cell(i,1).value
    age = sheet.cell(i,2).value
    values = (id,name,age)
    cur.execute(w,values)
    conn.commit()

cur.close()
conn.close()
colymns = str(sheet.ncols)
rows = str(sheet.nrows)
print('OK')
